package com.guojian.jdbc.sql;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.PropertyAccessorFactory;
import org.springframework.util.ReflectionUtils;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.guojian.common.map.TableMap;
import com.guojian.common.reflect.ReflectUtils;
import com.guojian.common.utils.StringUtilss;
import com.guojian.jdbc.annotation.SqlExp;
import com.guojian.jdbc.annotation.TestBean;

import lombok.extern.slf4j.Slf4j;

/**
 * guojian sql构造器,自动生成带条件的sql
 */
@Slf4j
public class SqlBuilder {

	public static String getApproveRoomBaseSql() {
		StringBuilder stringBuilder = new StringBuilder();
		stringBuilder.append(" ( select * from (select a.*,ROW_NUMBER() OVER(PARTITION BY room_id ORDER BY update_time DESC) rn FROM QIYE_ROOM_APPROVE a) where rn = 1 ) ");
		return stringBuilder.toString();
	}

	public static void buildParamsByMap(Map<String, String> paramsMap, List<Object> sqlParams, StringBuilder sqlBuilder, String paramName, String sql) {
		String paramValue = paramsMap.get(paramName);
		if (StringUtils.isNotBlank(paramValue) && !"null".equalsIgnoreCase(paramValue)) {
			sqlBuilder.append(sql);
			sqlParams.add(paramValue);
		}
	}

	public static void buildSelectParams(StringBuilder sqlBuilder, TableMap<String, SqlExpression, Object> tableMap, List<Object> sqlParams) {
		/*
		 * for (String rowKey : tableMap.keySet()) { List<Map<SqlExpression,
		 * Object>> ListRowMap = tableMap.get(rowKey); for (Map<SqlExpression,
		 * Object> rowMap : ListRowMap) { for (SqlExpression columnKey :
		 * rowMap.keySet()) { Object value = rowMap.get(columnKey); if
		 * (ObjectUtils.isNotEmpty(value) &&
		 * !"null".equalsIgnoreCase(value.toString())) {
		 * sqlBuilder.append(" and " + rowKey + columnKey.getValue() + " ? ");
		 * if
		 * (columnKey.getValue().equalsIgnoreCase(SqlExpression.LIKE.getValue())
		 * ) { value = "%" + value + "%"; } sqlParams.add(value); } } } }
		 */

		for (String rowKey : tableMap.keySet()) {
			Map<SqlExpression, Object> columnRowMap = tableMap.get(rowKey);
			for (SqlExpression columnKey : columnRowMap.keySet()) {
				Object value = columnRowMap.get(columnKey);
				if (ObjectUtils.isNotEmpty(value) && !"null".equalsIgnoreCase(value.toString())) {

					if (!sqlBuilder.toString().toLowerCase().contains("where")) {
						sqlBuilder.append(" where 1=1 ");
					}

					sqlBuilder.append(" and " + rowKey + columnKey.getValue() + " ? ");
					if (columnKey.getValue().equalsIgnoreCase(SqlExpression.LIKE.getValue())) {
						value = "%" + StringUtilss.toString(value) + "%";
					}
					sqlParams.add(value);
				}
			}
		}

	}

	public static <T> void buildSelectParams(StringBuilder sqlBuilder, T object, List<Object> sqlParams) {

		Class<?> tClass = object.getClass();
		Field[] fields = ReflectUtils.getDeclaredFields(tClass);
		SqlExp sqlExpClass = tClass.getAnnotation(SqlExp.class);

		String classAlias = StringUtils.EMPTY;

		if (sqlExpClass != null) {
			classAlias = sqlExpClass.alias();
		}

		for (Field field : fields) {
			ReflectionUtils.makeAccessible(field);
			String columnName = field.getName();

			SqlExp sqlExp = field.getAnnotation(SqlExp.class);
			if (sqlExp == null) {
				continue;
			}

			String fieldAlias = sqlExp.alias();
			if (StringUtilss.isBlank(fieldAlias)) {
				fieldAlias = classAlias;
			}

			if (StringUtilss.hasText(fieldAlias)) {
				columnName = fieldAlias + "." + columnName + " ";
			}

			if (!sqlBuilder.toString().toLowerCase().contains("where")) {
				sqlBuilder.append(" where 1=1 ");
			}

			try {
				Object fieldValue = field.get(object);
				if (fieldValue != null) {
					sqlBuilder.append(" and " + columnName + sqlExp.exp().getValue() + " ? ");
					sqlParams.add(fieldValue);
				}
			} catch (IllegalAccessException e) {
				log.debug("buildSelectParams异常,Class: {},异常: ", object.getClass(), e.getMessage());
			}

		}

	}

	@SuppressWarnings("unused")
	private static <T> void test2(T object) {
		BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(object);
		PropertyDescriptor[] propertyDescriptors = bw.getPropertyDescriptors();
		System.out.println("object.getClass() = " + object.getClass());

		for (PropertyDescriptor pd : propertyDescriptors) {
			if (pd.getReadMethod() != null) {
				System.out.println(pd.getName() + "== " + bw.getPropertyValue(pd.getName()));
			}
		}
	}

	public static void main(String[] args) {

		TestBean testBean = new TestBean();
		// testBean.setId(111);
		testBean.setAge(22);
		testBean.setName("guojian");
		testBean.setBeginDate("2020-10-01");
		testBean.setEndDate("2020-11-01");

		StringBuilder stringBuilder = new StringBuilder(" select * from test  where abc>1 ");
		List<Object> params = Lists.newArrayList();

		buildSelectParams(stringBuilder, testBean, params);

		System.out.println("stringBuilder = " + stringBuilder);
		System.out.println("params = " + JSON.toJSONString(params));

	}

	@SuppressWarnings("unused")
	private static void test() {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from a where 1=1 ");

		System.out.println("sb.indexOf(\"wherew\") = " + sb.indexOf("wherew"));

		Map<String, List<Map<SqlExpression, Object>>> mapParams = Maps.newLinkedHashMap();
		Map<SqlExpression, Object> map1 = Maps.newHashMap();
		map1.put(SqlExpression.LIKE, null);

		mapParams.put("name", Arrays.asList(map1));
		mapParams.put("date", Arrays.asList(ImmutableMap.of(SqlExpression.EQUAL_OR_GREATER, "2020-11-11"), ImmutableMap.of(SqlExpression.EQUAL_OR_LESSER, "2020-11-12")));
		mapParams.put("age", Arrays.asList(ImmutableMap.of(SqlExpression.IS_NOT, 5576)));

		List<Object> sqlParams = Lists.newArrayList();

		// SysOperLog log = new SysOperLog();
		// log.setOperName("1111");
		// log.setOperId(1L);
		// log.setDeptName("abc");

		// System.out.println("sb = " + sb);

		TableMap<String, SqlExpression, Object> tableMap = new TableMap<>();
		tableMap.put("a.date", SqlExpression.EQUAL_OR_GREATER, "2020-11-10");
		tableMap.put("a.date", SqlExpression.EQUAL_OR_LESSER, "2020-11-14");
		tableMap.put("b.name", SqlExpression.LIKE, "guojian");
		tableMap.put("c.name", SqlExpression.LIKE, "");

		buildSelectParams(sb, tableMap, sqlParams);
		System.out.println("sql = " + sb);
		System.out.println("sqlparams = " + JSON.toJSONString(sqlParams));
	}

}
